package com.webapp.model;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.StringTokenizer;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class UploadModel {
	
	private static Connection conn = null;
	private static String driver = "com.mysql.jdbc.Driver";
	private static String dbUrl = "jdbc:mysql://mysql1.cs.stonybrook.edu:3306/gdonnelly";
	private static String dbPassword = "106338300";
	private static String dbUser = "gdonnelly";
	
	private static String macAY = "/Users/diodaton/Desktop/ay.txt";
	private static String macAY_Map = "/Users/diodaton/Desktop/ay_map.txt";
	
	private static String windowsAY = "C:\\Users\\Nick\\Desktop\\AY.txt";
	private static String windowAY_map = "C:\\Users\\Nick\\Desktop\\AY-map.txt";
	
	private static Application app;
	
	public UploadModel(){
		app = new Application();
	}
	
	public static void main(String [] args){
		UploadModel l = new UploadModel();
		l.uploadAY(macAY, macAY_Map);
	}
	

	/*
	 * Checks the mapping of the AY.txt and the AY-map.txt files. 
	 * RETURNS true if mapping is correct, otherwise false.
	 */
	public static boolean checkMappingAY(String ayFile, String ayMapFile){
		try{
			FileInputStream AY = new FileInputStream(ayFile);
			FileInputStream AY_map = new FileInputStream(ayMapFile);
			
			DataInputStream AYin = new DataInputStream(AY);
			DataInputStream AY_mapin = new DataInputStream(AY_map);
			
			BufferedReader AYbr = new BufferedReader(new InputStreamReader(AYin));
			BufferedReader AY_mapbr = new BufferedReader(new InputStreamReader(AY_mapin));
			
			boolean resultAY = true;
			
			//Checks the mapping of the AY file compared to the AY_MAP file.
			String ayLine;
				if((ayLine = AYbr.readLine()) != null){
					
					String endCase = " ";
					String testCase;
					int indexOne;
					int indexTwo = -1;
					
					while(!(endCase.equals("Year7GPA"))){
						indexOne = ayLine.indexOf("\"", indexTwo+1);
						indexTwo = ayLine.indexOf("\"", indexOne+1);
						
						//get current "term" of AY file
						endCase = ayLine.substring(indexOne+1, indexTwo);
						
						String tempLine;
						//get current "term" from AY-Map
						if((tempLine = AY_mapbr.readLine()) != null){
							int indexEnd = tempLine.indexOf("->");
							testCase = tempLine.substring(0, indexEnd);
							
							//verify that both match, if no match, return false and break the loop
							if(!(testCase.equals(endCase))){
								resultAY = false;
								break;
							}
						}
					}
				}
				
				if(resultAY)
					return true;
				else
					return false;
				
		}catch(IOException ex){return false;}	
	}
	
	/*
	 * Checks the mapping of the OTS.txt and OTS-Map.txt files.
	 * RETURNS true if mapping is correct, otherwise false
	 * 
	 */
	public static boolean checkMappingOTS(String otsFile, String otsMapFile){
		try{
			FileInputStream OTS = new FileInputStream(otsFile);
			FileInputStream OTS_map = new FileInputStream(otsMapFile);
			
			DataInputStream OTSin = new DataInputStream(OTS);
			DataInputStream OTS_mapin = new DataInputStream(OTS_map);
			
			BufferedReader OTSbr = new BufferedReader(new InputStreamReader(OTSin));
			BufferedReader OTS_mapbr = new BufferedReader(new InputStreamReader(OTS_mapin));
			
			boolean resultOTS = true;

				//Checks the mapping of the OTS file compared to the OTS_MAP file
				String otsLine;
				if((otsLine = OTSbr.readLine()) != null){
					
					String endCase = " ";
					String testCase;
					int indexOne;
					int indexTwo = -1;
					
					while(!(endCase.equals("Off_TOEFL_Wri"))){
						indexOne = otsLine.indexOf("\"", indexTwo+1);
						indexTwo = otsLine.indexOf("\"", indexOne+1);
						
						//get current "term" of OTS file
						endCase = otsLine.substring(indexOne+1, indexTwo);
						
						String tempLine;
						//get current "term" from OTS-Map
						if((tempLine = OTS_mapbr.readLine()) != null){
							int indexEnd = tempLine.indexOf("->");
							testCase = tempLine.substring(0, indexEnd);
							
							//verify that both match, if no match, return false and break the loop
							if(!(testCase.equals(endCase))){
								resultOTS = false;
								break;
							}
						}
					}
				}
				
				if(resultOTS)
					return true;
				else
					return false;
				
		}catch(IOException ex){return false;}	
	}
	
	/*
	 * Method that adds a new AY file to the GARS database.
	 * NOTE: our version of GARS does not allow for updating previously existing applications.
	 */
	public boolean uploadAY(String ayFile, String ayMapFile){
		try{
			FileInputStream fi = new FileInputStream(ayFile);
			DataInputStream fiin = new DataInputStream(fi);
			BufferedReader fibr = new BufferedReader(new InputStreamReader(fiin));
			
			if(checkMappingAY(ayFile, ayMapFile)){
				Statement stmt = connect();
				fibr.readLine();
				String line;
				
				while((line = fibr.readLine()) != null){
					String[] result = parseString(line);
					for(int i = 0; i < result.length; i++){
						System.out.println("Index = " +i+ " Value = " +result[i]);
					}
					if(!(stmt.executeQuery("SELECT * FROM app_general_info WHERE applicant_email = '" +result[14]+ "'").next())){
						
						/* INSERTS: Email, ID, Upload Date
						 * RESULT: 14, 1, 0
						 */
						stmt.executeQuery("INSERT INTO app_general_info (applicant_email, applicant_ID, "+
										"application_file_upload_date) VALUES ('" +result[14]+ "', '" +result[1]+
										"', '" +result[0]+ "')");
						
						/*
						 * INSERTS: Email, ID, [Theory/Algorithm/Compiler/OS (Name, Scale, Grade, SBU Equiv.)] 
						 * RESULT: 14, 1, 39, 43, 44, 45, 46, 50, 51, 52, 53, 57, 58, 59, 60, 64, 65, 66 
						 */
						stmt.executeQuery("INSERT INTO ay_applicant_courses (applicant_email, applicant_ID, course_theory, "+
										"course_theory_grade_scale, course_theory_grade, sbu_equivalent_theory, course_algo, "+
										"course_algo_grade_scale, course_algo_grade, sbu_equivalent_algo, course_ppl_compilers, "+
										"course_ppl_compilers_grade_scale, course_ppl_compilers_grade, sbu_equivalent_ppl_compilers, "+
										"course_os, course_os_grade_scale, course_os_grade, sbu_equivalent_os) VALUES ('"+
										result[14]+ "', '" +result[1]+ "', '" +
										result[39]+ "', '" +result[43]+ "', '" +result[44]+ "', '" +result[45]+
										result[46]+ "', '" +result[50]+ "', '" +result[51]+ "', '" +result[52]+
										result[53]+ "', '" +result[57]+ "', '" +result[58]+ "', '" +result[59]+
										result[60]+ "', '" +result[64]+ "', '" +result[65]+ "', '" +result[66]+ "')");
						
						/*
						 * INSERTS: Email, ID, Research Area, Research Topics, Undergrad Inst, Grad Inst
						 * RESULT: 14, 1, 33, 34, 67, 75
						 */
						stmt.executeQuery("INSERT INTO ay_applicant_edu_background (applicant_email, applicant_ID, research_area"+
											", research_topics, undergrad_institution, grad_institution) VALUES ('" +
											result[14]+ "', '" +result[1]+ "', '" +result[33]+ "', '" +result[34]+ "', '" +
											result[67]+ "', '" +result[75]+ "')");
						
						/*
						 * INSERTS: Email, ID, First/Middle/Last Name, Birth Month/Day/Year, Gender, Ethnicity, Race, 
						 * 			Citizenship, Perm. Resident, Phone Number, Application Type
						 * RESULT: 14, 1, 3, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15
						 */
						stmt.executeQuery("INSERT INTO ay_applicant_general_info (applicant_email, applicant_ID, applicant_first_name"+
											", applicant_last_name, applicant_middle_name, applicant_birth_month, applicant_birth_day"+
											", applicant_birth_year, applicant_gender, applicant_ethnicity, applicant_race"+
											", applicant_citizenship, applicant_perm_resident, applicant_phone_num, applicantion_type)"+
											"VALUES ('" +result[14]+ "', '" +result[1]+ "', '" +result[3]+ "', '" +result[2]+ "', '"+
											result[4]+ "', '" +result[5]+ "', '" +result[6]+ "', '" +result[7]+ "', '" +result[8]+ "', '"+
											result[9]+ "', '" +result[10]+ "', '" +result[11]+ "', '" +result[12]+ "', '"+
											result[13]+ "', '" +result[15]+ "')");
						
						/*
						 * INSERTS: Email, ID, Grad Inst, Grad GPA, Grad GPA Scale, Grad year6 GPA, Grad year7 GPA, Grad Rank, Grad Rank Out Of
						 * RESULT: 14, 1, 75, 76, 77, 78, 79, 37, 38
						 */
						stmt.executeQuery("INSERT INTO ay_applicant_grad_insititution (applicant_email, applicant_ID, grad_insititution"+
										", grad_gpa, grad_scale, grad_year6_gpa, grad_year7_gpa, grad_rank, grad_rank_out_of) VALUES ('"+
										result[14]+ "', '" +result[1]+ "', '" +result[75]+ "', '" +result[76]+ "', '" +result[77]+ "', '"+
										result[78]+ "', '" +result[79]+ "', '" +result[37]+ "', '" +result[38]+ "')");
						
						/*
						 * INSERTS: Email, ID, GRE Score verbal/quant/analy, GRE percentile verbal/quant/analy, GRE Subject name/score
						 * RESULT: 14, 1, 16, 17, 18, 19, 20, 21, 22, 23
						 */
						stmt.executeQuery("INSERT INTO ay_applicant_gre (applicant_email, applicant_ID, gre_verbal, gre_verbal_percentile, gre_quant" +
										", gre_quant_percentile, gre_analy, gre_analy_percentile, gre_test, gre_test_score) VALUES ('"+
										result[14]+ "', '" +result[1]+ "', '" +result[16]+ "', '" +result[17]+ "', '" +result[18]+ "', '" +
										result[19]+ "', '" +result[20]+ "', '" +result[21]+ "', '" +result[22]+ "', '" +result[23]+ "')");
						
						/*
						 * INSERTS: Email, ID, TOEFL Internet, IELTS Score, TOEFL Score
						 * RESULT: 14, 1, 32, 26, 24
						 */
						stmt.executeQuery("INSERT INTO ay_applicant_toefl (applicant_email, applicant_ID, TOELF_Internet, IELTS_score"+
										", TOEFL_score) VALUES ('" +result[14]+ "', '" +result[1]+ "', '" +result[32]+ "', '"+
										result[26]+ "', '" +result[24]+ "')");
						
						/*
						 * INSERTS: Email, ID, Undergrad Inst/GPA/GPA Scale/GPA1/GPA2/GPA3/GPA4/GPA5/Rank/Rank Out Of
						 * RESULT: 14, 1, 67, 68, 69, 70, 71, 72, 73, 74, 35, 36
						 */
						stmt.executeQuery("INSERT INTO ay_applicant_ug_insititute (applicant_email, applicant_ID, ug_insititution, ug_gpa"+
										", ug_scale, ug_year1_gpa, ug_year2_gpa, ug_year3_gpa, ug_year4_gpa, ug_year5_gpa, ug_rank, ug_rank_out_of)"+
										" VALUES ('" +result[14]+ "', '" +result[1]+ "', '" +result[67]+ "', '" +result[68]+ "', '" +result[69]+
										"', '" +result[70]+ "', '" +result[71]+ "', '" +result[72]+ "', '" +result[73]+ "', '" +result[74]+ "', '"+
										result[35]+ "', '" +result[36]+ "')");				
					}
				}
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	/*
	 * Method that adds a new AY file to the GARS database.
	 * NOTE: our version of GARS does not allow for updating previously existing applications.
	 */
	public boolean uploadOTS(String otsFile, String otsMapFile){
		try{
			FileInputStream fi = new FileInputStream(otsFile);
			DataInputStream fiin = new DataInputStream(fi);
			BufferedReader fibr = new BufferedReader(new InputStreamReader(fiin));
			
			if(checkMappingOTS(otsFile, otsMapFile)){
				Statement stmt = connect();
				fibr.readLine();
				String line;
				
				while((line = fibr.readLine()) != null){
					String[] result = parseString(line);
					for(int i = 0; i < result.length; i++){
						System.out.println("Index = " +i+ " Value = " +result[i]);
					}
					if(!(stmt.executeQuery("SELECT * FROM ots_applicant_general_info WHERE applicant_email = '" +result[2]+ "'").next())){
						ResultSet rs = stmt.executeQuery("SELECT applicant_ID FROM app_general_info WHERE applicant_email = '" +result[2]+ "'");
						int appID = 0;
						if(rs.next())
							appID = rs.getInt(1);
						
						/*
						 * INSERT: Email, ID, First/Last Name, Citizenship, Day/Month/Year Birth, Gender
						 * RESULT: 0, 1, 2, 3, 4, 5, 6, 7
						 */
						stmt.executeQuery("INSERT INTO ots_applicant_general_info (applicant_email, applicant_ID, applicant_first_name, applicant_last_name"+
									", applicant_citizenship, applicant_date_of_birth, applicant_month_of_birth, applicant_year_of_birth, applicant_gender) VALUES ('"+
									result[2]+ "', '" +appID+ "', '" +result[1]+ "', '" +result[0]+ "', '" +result[3]+ "', '" +result[5]+ "', '" +result[4]+ "', '"+
									result[6]+ "','" +result[7]+ "')");
						
						/*
						 * INSERT: Email, ID, GRE V/Q/A, GRE Percentile V/Q/A, GRE Subj Score/Percent/Name
						 * RESULT: 2, 8, 9, 10, 11, 12, 13, 14, 15, 16
						 */
						stmt.executeQuery("INSERT INTO ots_applicant_gre_score (applicant_email, applicant_ID, off_gre_v, off_gre_vp, off_gre_q, off_gre_qp"+
										", off_gre_a, off_gre_ap, off_gre_subj, off_gre_subjp, off_gre_subjname) VALUES ('" +result[2]+ "', '" +appID+ "', '"+
										result[8]+ "', '" +result[9]+ "', '" +result[10]+ "', '" +result[11]+ "', '" +result[12]+ "', '" +result[13]+ "', '"+
										result[14]+ "', '" +result[15]+ "', '" +result[16]+ "')");
							
						/*
						 * INSERT: Email, ID, TOEFL total/listen/reading/speaking/writing
						 * RESULT: 2, 17, 18, 19, 20, 21
						 */
						stmt.executeQuery("INSERT INTO ots_applicant_toefl_scores (applicant_email, applicant_ID, off_toelf_total, off_toelf_lis, off_toelf_rd"+
										", off_toelf_spk, off_toelf_wri) VALUES ('" +result[2]+ "', '" +appID+ "', '" +result[17]+ "', '" +result[18]+ "', '"+
										result[19]+ "', '" +result[20]+ "', '" +result[21]+ "')");
					}
				}
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	public static String[] parseString(String file){
		
		//splits up all the strings into an array
		String [] result = file.split("\t");

		//removes all quotes around the values, if they have quotes
		for(int i = 0; i < result.length; i++){
			if((result[i].contains("\"")) && (!result[i].equals(""))){
				int indexOne = result[i].indexOf("\"");
				int indexTwo = result[i].lastIndexOf("\"");
				indexOne++;
					
				result[i] = result[i].substring(indexOne, indexTwo);
			}
		}
		return result;
	}
	
	public static Statement connect(){
		try{
			Class.forName(driver);
			conn = (Connection) DriverManager.getConnection(dbUrl, dbUser, dbPassword);
			Statement stmt = (Statement) conn.createStatement();
			
			return stmt;
		}catch(Exception ex){return null;}
	}

}
